VBA vs Python语法双语对照学习: Data Types of Variables

Each variable has its own data type (e.g., numeric, string). This section mainly introduces basic data types and type conversion in Excel VBA and Python.

Basic Data Types

Excel VBA

Common data types in Excel VBA include Boolean, Byte, String, Date, Variant, and Object, as shown in Table 2-2.

Table 2-2: Common Data Types in Excel VBA

Data Type	Name	Variable Naming Prefix	Storage Size	Description	VarType Return Value
Boolean	Boolean	bln	2 bytes	16-bit, value is True or False	11
Byte	Character	byt	1 byte	8-bit unsigned integer	17
Integer	Short Integer	int	2 bytes	16-bit integer	2
Long	Long Integer	lng	4 bytes	32-bit integer	—
Single	Single-Precision Float	sng	4 bytes	32-bit real number	4
Double	Double-Precision Float	dbl	8 bytes	64-bit real number	5
String	String	str	String size	String value	8
String*n	Fixed-Length String	—	—	Fixed-length string value	—
Currency	Currency	cur	8 bytes	64-bit fixed-point real number	6
Date	Date	dat	8 bytes	64-bit real number (date/time)	7
Variant	Variant	var	Variable	Can represent any of the above types	12
User Type	Custom Type	—	—	Custom type defined by Type	36
Object	Object	obj	4 bytes	32-bit object reference	9

For a given variable, Excel VBA provides functions to check its data type. Use TypeName and VarType to return the data type name and value of the variable. Use IsNumeric to check if the variable is numeric or currency type, IsDate to check if it is a date type, IsEmpty to check if it has been initialized, and IsNull to check if it has a valid value. The sample file path is Samples\ch02\Excel VBA\DataTypes.xlsm.

code.vba

Sub Test()
    Dim intA As Integer
    Dim bolB As Boolean
    Dim strC As String
    Dim datD As Date
    Dim varF As Variant
    intA = 8
    bolB = True
    strC = "Hello"
    datD = "05/25/2021"
    varF = Null
    Debug.Print TypeName(bolB)   ' Returns the data type name of bolB
    Debug.Print VarType(intA)   ' Returns the data type of intA(as a number)
    Debug.Print IsNumeric(intA)   ' Checks if intA is numeric
    Debug.Print IsDate(datD)   ' Checks if datD is a date
    Debug.Print IsEmpty(lngE)   ' Checks if lngE is initialized
    Debug.Print IsNull(varF)   ' Checks if varF has a valid value
End Sub

Running the subroutine outputs the following in the Immediate Window:

code.vba

Boolean
2
True
True
True
True

VarType returns 2, indicating intA is a short integer. Note: If lngE is not declared, IsEmpty returns True (not initialized); if declared, it returns False (initialized).

Python

Common data types in Python include Boolean, numeric, string, list, tuple, etc., as shown in Table 2-3. Thus, Python 3 has no distinction between short/long integers or single/double-precision floats.

Table 2-3: Common Data Types in Python

Type Name	Type Character	Description	Example
Boolean	bool	Value is True or False	>>> a = True; b = False
Integer	int	Represents integers, no size limit (can represent very large numbers)	>>> a = 1; b = 10000000
Float	float	Decimal numbers, can be in scientific notation	>>> a = 1.2; b = 1.2e3
String	str	Character sequence, elements are immutable	>>> a = 'A'; b = 'A'
List	list	Elements can be different types, ordered, mutable, and repeatable	>>> a = [1, 'A', 3.14, []]
Tuple	tuple	Similar to list, elements are immutable	`>>> a = (1, 'A', 3.14, ())`
Dictionary	dict	Unordered collection of key-value pairs, mutable, keys are unique	>>> a = {1: 'A', 2: 'B'}
Set	set	Unordered, mutable, and non-repeating elements	>>> a = {1, 3.14, 'name'}
None	NoneType	Represents an empty object	>>> a = None

In Python, use the type function to return a variable’s data type. You can also use isinstance to check if a variable is a specified data type.

code.python

>>> a = 12.3
>>> b = 'Hello'
>>> type(a)
<class 'float'>
>>> isinstance(b, str)
True

type shows a is a float, and isinstance confirms b is a string.

Data Type Conversion

Excel VBA

In Excel VBA, type conversion has two methods: explicit and implicit. Explicit conversion uses a series of conversion functions (listed in Table 2-4). These functions usually start with C, followed by the abbreviation of the target data type (e.g., CInt for short integer, where Int is the abbreviation of Integer).

Table 2-4: Conversion Functions in Excel VBA

Function	Syntax	Functionality	Parameters
CBool	CBool(Num	$)	Converts to Boolean: 0 → False, others → True
CByte	CByte(Num	$)	Converts to Byte
CCur	CCur(Num	$)	Converts to Currency
CDate	CDate(Num	$) or CVDate(Num	$)
CDbl	CDbl(Num	$)	Converts to double-precision float
CInt	CInt(Num	$)	Converts to 16-bit short integer (overflow error if too large/small)
CLng	CLng(Num	$)	Converts to 32-bit long integer (overflow error if too large/small)
CSng	CSng(Num	$)	Converts to single-precision float (overflow error if too large/small)
CStr	CStr(Num	$)	Converts to string
CVar	CVar(Num	$)	Converts to Variant
Val	Val(S$)	Returns the numeric value of S$	S$: String to return the numeric value (octal if starts with &O, hex if &H)

Below, use CSng to convert a short integer to a single-precision float:

code.vba

Dim intA As Integer
Dim sngB As Single
intA = 10
sngB = CSng(intA)

You can also use implicit conversion:

code.vba

sngB = intA

Python

Common type conversion functions in Python are listed in Table 2-5.

Table 2-5: Common Type Conversion Functions in Python

Function	Description
int(x [,base])	Converts object x to integer
float(x)	Converts object x to float
complex(real [,imag])	Creates a complex number
str(x)	Converts object x to string
repr(x)	Converts object x to an expression string
eval(str)	Evaluates a valid Python expression in string str and returns an object
tuple(s)	Converts sequence s to a tuple
list(s)	Converts sequence s to a list
set(s)	Converts sequence s to a mutable set
dict(d)	`Creates a dictionary(d must be a sequence of(key, value) tuples)`

Examples of type conversion:

code.python

>>> a = 10
>>> b = float(a)   # Convert to float
>>> b
10.0
>>> type(b)
<class 'float'>
>>> c = complex(a, -b)   # Create complex number with a and b
>>> c
(10-10j)
>>> type(c)
<class 'complex'>
>>> d = str(a)    # Convert to string
>>> d
'10'
>>> type(d)
<class 'str'>

After type conversion, a new object is created in memory, not modifying the original object’s value. Use id to check the memory addresses of a, b, c, and d:

code.python

>>> id(a)
8791516675424
>>> id(b)
51490992
>>> id(c)
51490960
>>> id(d)
49152816

Thus, each variable has a different memory address—conversion creates a new object.

Table of Contents

Basic Data Types

Data Type Conversion